package com.utils;

import com.domain.ex.BizException;
import com.domain.ex.Codes;
import com.google.common.collect.Lists;
import com.pojo.ShRiskUser;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Created by Administrator on 2016/1/10 0010.
 * POI操作excel
 */
public class ExcelUtils {


    /**
     * 统一方法导入excel，将excel读如2 x 2的list中
     *
     * @param inputStream 导入文件
     * @return
     */
    public static List<List<String>> importExcel(InputStream inputStream) {

        if (inputStream == null) {
            return null;
        }
        List<List<String>> list = Lists.newArrayList();

        try {
            // 工作薄
            Workbook workbook = null;
            try {
                workbook = new XSSFWorkbook(inputStream);
            } catch (Exception ex) {
                workbook = new HSSFWorkbook(inputStream);
            }
            if (workbook.getNumberOfSheets() < 1) {
                throw BizException.create(Codes.ExcelParseError);
            }
            // 第一页
            Sheet sheet = workbook.getSheetAt(0);
            // 行迭代器
            Iterator<Row> rows = sheet.rowIterator();
            if (rows.hasNext()) {
                // - 跳过表头
                rows.next();
            }
            while (rows.hasNext()) {
                Row row = (Row) rows.next();
                // 列迭代器
                Iterator<Cell> cells = row.cellIterator();
                // 每一行是一个对象
                List<String> subList = Lists.newArrayList();
                // 遍历单元格
                while (cells.hasNext()) {
                    Cell cell = (Cell) cells.next();
                    subList.add(getCellValue(cell));
                }
                list.add(subList);
            }
        } catch (FileNotFoundException e) {
            throw BizException.create(Codes.ExcelParseError, e.getMessage());
        } catch (IOException e) {
            throw BizException.create(Codes.ExcelParseError, e.getMessage());
        }

        return list;
    }

    /**
     * 通用方法导出excel
     *
     * @param data  数据集
     * @param title 表头
     * @param fileName  导出文件名
     * @param <T>   泛型
     * @return
     */
    public static <T> ResponseEntity<byte[]> exportExcel(Collection<T> data, Collection<String> title,String fileName) {

        if (CollectionUtils.isEmpty(title)) {
            return null;
        }
        String[] header = title.toArray(new String[title.size()]);
        // - 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // - 创建工作表
        Sheet sheet = workbook.createSheet();

        // - 字体
        Font headfont = workbook.createFont();
        headfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // - 样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(headfont);

        // - 第一行表头
        // 产生表格标题行
        Row row = sheet.createRow(0);
        Iterator<String> iterator = title.iterator();
        int count = 0;
        while (iterator.hasNext()) {
            // - 列宽自动适应
            sheet.autoSizeColumn((short) count);
            String temp = iterator.next();
            Cell cell = row.createCell(count);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(temp);
            count++;
        }

        // - 读取数据集数据集
        if (!CollectionUtils.isEmpty(data)) {
            // - 没有数据集时为导出模版，写一条范例数据
            Iterator<T> iterator1 = data.iterator();
            int index = 1;
            while (iterator1.hasNext()) {
                row = sheet.createRow(index++);
                T t = (T) iterator1.next();
                Field[] fields = t.getClass().getDeclaredFields();
                // - 通过反射遍历对象的值并写入cell
                for (int i = 0; i < fields.length; i++) {
                    Cell cell = row.createCell(i);
                    Field field = fields[i];
                    String fieldName = field.getName();
                    String getMethodName = "get"
                            + fieldName.substring(0, 1).toUpperCase()
                            + fieldName.substring(1);
                    Class clazz = t.getClass();
                    try {
                        Method method = clazz.getMethod(getMethodName,new Class[]{});
                        Object object = method.invoke(t,new Object[]{});
                        if (object == null){
                            cell.setCellValue("");
                        }else {
                            cell.setCellValue(object.toString());
                        }
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }


        } else {
            // - 有数据时通过反射些数据

        }

        // - 导出
        if (workbook == null) {
            return null;
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);

            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            headers.setContentDispositionFormData("attachment", fileName);
            return new ResponseEntity<byte[]>(os.toByteArray(), headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
            throw BizException.create(Codes.TemplateExportError, e.getMessage());
        } finally {
            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    throw BizException.create(Codes.TemplateExportError, e.getMessage());
                }
            }
        }
    }


    /**
     * 读取单元格的值
     *
     * @param cell
     * @return
     * @Title: getCellValue
     * @Date : 2014-9-11 上午10:52:07
     */
    public static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            // - 将所有的内容都作为字符串原样输出
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result = cell.getStringCellValue();
        }
        return result.toString();
    }


}
